package com.lotea.android.andreaditlater.data.db;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;

import com.lotea.android.andreaditlater.utils.Log;

public class DbAdapter {

	// Tables
	public interface Tables {
		public static final String ITEMS = "items";
		public static final String TAGS = "tags";
		public static final String REL_ITEMS_TAGS = "rel_items_tags";
	}

	// Columns
	public interface Columns {
		public static final String ID = "_id";

		public interface Items {
			public static final String TITLE = "title";
			public static final String URL = "url";
			public static final String TIME_UPDATED = "time_update";
			public static final String TIME_ADDED = "time_added";
			public static final String STATE = "state";
		}

		public interface Tags {
			public static final String NAME = "name";
		}

		public interface RelItemsTags {
			public static final String ID_ITEM = "id_item";
			public static final String ID_TAG = "id_tag";
		}
	}

	// Columns
	public interface Indexes {
		public interface Items {
			public static final int ID = 1;
			public static final int STATE = 2;
			public static final int TIME_ADDED = 3;
			public static final int TIME_UPDATED = 4;
			public static final int TITLE = 5;
			public static final int URL = 6;
		}

		public interface Tags {
			public static final int NAME = 1;
		}

		public interface RelItemsTags {
			public static final int ID_ITEM = 1;
			public static final int ID_TAG = 2;
		}
	}

	public static final String DATABASE_NAME = "database";
	private static final int DATABASE_VERSION = 1;

	/**
	 * Table creation sentences
	 */
	private static final String DATABASE_CREATE_ITEMS = "create table "
			+ Tables.ITEMS + " (" + Columns.ID + " integer primary key, "
			+ Columns.Items.TITLE + " text not null, " + Columns.Items.URL
			+ " text not null, " + Columns.Items.TIME_ADDED
			+ " integer not null, " + Columns.Items.TIME_UPDATED
			+ " integer not null, " + Columns.Items.STATE
			+ " integer not null);";

	private static final String DATABASE_CREATE_TAGS = "create table "
			+ Tables.TAGS + " (" + Columns.ID
			+ " integer primary key autoincrement, " + Columns.Tags.NAME
			+ " text unique not null);";

	private static final String DATABASE_CREATE_RELITEMSTAGS = "create table "
			+ Tables.REL_ITEMS_TAGS + " (" + Columns.ID
			+ " integer primary key autoincrement, "
			+ Columns.RelItemsTags.ID_ITEM + " integer not null, "
			+ Columns.RelItemsTags.ID_TAG + " integer not null, unique("
			+ Columns.RelItemsTags.ID_ITEM + ", " + Columns.RelItemsTags.ID_TAG
			+ "));";

	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;

	private final Context mContext;

	/**
	 * Helper para creaci�n y gesti�n de la base de datos
	 * 
	 * @author Miguel Barrios
	 * 
	 */
	private static class DatabaseHelper extends SQLiteOpenHelper {

		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		/**
		 * Se lanza al abrir la base de datos y crea las tablas y las rellena
		 * con los datos iniciales
		 */
		@Override
		public void onCreate(SQLiteDatabase db) {
			db.execSQL(DATABASE_CREATE_ITEMS);
			db.execSQL(DATABASE_CREATE_TAGS);
			db.execSQL(DATABASE_CREATE_RELITEMSTAGS);
		}

		/**
		 * Actualiza la base de datos, borrando todo lo que hubiera en ella *
		 */
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.i("Upgrading database from version ", Integer
					.toString(oldVersion), " to ",
					Integer.toString(newVersion),
					", which will destroy all old data");

			db.execSQL("DROP TABLE IF EXISTS " + Tables.REL_ITEMS_TAGS);
			db.execSQL("DROP TABLE IF EXISTS " + Tables.TAGS);
			db.execSQL("DROP TABLE IF EXISTS " + Tables.ITEMS);
			onCreate(db);
		}
	}

	/**
	 * Constructora del Adapter, asigna el contexto
	 * 
	 * @param ctx
	 *            Contexto al que se asocia la base de datos
	 */
	public DbAdapter(Context ctx) {
		mContext = ctx;
	}

	/**
	 * Abre la base de datos en modo escritura
	 * 
	 * @return El Adapter con la base de datos abierta en modo escritura
	 * 
	 * @throws SQLException
	 *             Si hay alg�n problema al abrir la base de datos
	 */
	public DbAdapter open() throws SQLException {
		mDbHelper = new DatabaseHelper(mContext);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}

	/**
	 * Cierra la base de datos
	 * 
	 */
	public void close() {
		if (mDbHelper != null && mDb != null) {
			mDbHelper.close();
			mDb.close();
		}
	}

	/**
	 * Comprueba si la base de datos est� abierta
	 * 
	 * @return TRUE si est� abierta, FALSE en caso contrario
	 */
	public boolean isOpen() {
		return mDb != null && mDb.isOpen();
	}

	/**
	 * Borra todos los datos de la BBDD
	 * 
	 */
	public void deleteDb() {
		mDb.execSQL("DROP TABLE IF EXISTS " + Tables.REL_ITEMS_TAGS);
		mDb.execSQL("DROP TABLE IF EXISTS " + Tables.TAGS);
		mDb.execSQL("DROP TABLE IF EXISTS " + Tables.ITEMS);
		mDbHelper.onCreate(mDb);
	}

	public Cursor rawQuery(String sql, String[] selectionArgs) {
		Cursor cursor = mDb.rawQuery(sql, selectionArgs);
		if (cursor != null) {
			cursor.moveToFirst();
		}
		
		return cursor;
	}

	public Cursor queryItems(String selection, String[] selectionArgs) {
		StringBuilder builder = new StringBuilder();

		builder.append("SELECT ");
		builder.append(Tables.ITEMS);
		builder.append('.');
		builder.append(Columns.ID);
		builder.append(", ");
		builder.append(Tables.ITEMS);
		builder.append('.');
		builder.append(Columns.Items.STATE);
		builder.append(", ");
		builder.append(Tables.ITEMS);
		builder.append('.');
		builder.append(Columns.Items.TITLE);
		builder.append(", ");
		builder.append(Tables.ITEMS);
		builder.append('.');
		builder.append(Columns.Items.URL);
		builder.append(", ");
		builder.append(Tables.ITEMS);
		builder.append('.');
		builder.append(Columns.Items.TIME_ADDED);
		builder.append(", ");
		builder.append(Tables.ITEMS);
		builder.append('.');
		builder.append(Columns.Items.TIME_UPDATED);
		builder.append(", tags FROM ");
		builder.append(Tables.ITEMS);
		builder.append(" LEFT JOIN (SELECT *, GROUP_CONCAT(");
		builder.append(Tables.TAGS);
		builder.append('.');
		builder.append(Columns.Tags.NAME);
		builder.append(", ', ') AS tags FROM ");
		builder.append(Tables.TAGS);
		builder.append(" LEFT JOIN ");
		builder.append(Tables.REL_ITEMS_TAGS);
		builder.append(" ON ");
		builder.append(Tables.REL_ITEMS_TAGS);
		builder.append('.');
		builder.append(Columns.RelItemsTags.ID_TAG);
		builder.append(" = ");
		builder.append(Tables.TAGS);
		builder.append('.');
		builder.append(Columns.ID);
		builder.append(" GROUP BY ");
		builder.append(Tables.REL_ITEMS_TAGS);
		builder.append('.');
		builder.append(Columns.RelItemsTags.ID_ITEM);
		builder.append(") AS TABLE_A ON TABLE_A.");
		builder.append(Columns.RelItemsTags.ID_ITEM);
		builder.append(" = ");
		builder.append(Tables.ITEMS);
		builder.append('.');
		builder.append(Columns.ID);
		builder.append(" ORDER BY ");
		builder.append(Tables.ITEMS);
		builder.append('.');
		builder.append(Columns.Items.TIME_ADDED);
		builder.append(" DESC");

		if (selection != null && selection.length() > 0) {
			builder.insert(0, "SELECT * FROM (");
			builder.append(") WHERE (");
			builder.append(selection);
			builder.append(')');
		}

		Cursor cursor = mDb.rawQuery(builder.toString(), selectionArgs);
		
		if (cursor != null)
			cursor.moveToFirst();
		
		return cursor;
	}

	/**
	 * Marca el inicio de una transacci�n
	 */
	public void beginTransaction() {
		mDb.beginTransaction();
	}

	/**
	 * Marca el fin de una transacci�n
	 */
	public void endTransaction() {
		mDb.endTransaction();
	}

	/**
	 * Confirma una transacci�n
	 */
	public void setTransactionSuccessful() {
		mDb.setTransactionSuccessful();
	}

	/**
	 * Compila una sentencia SQL para ser ejecutada
	 * 
	 * @param sql
	 *            Sentencia SQL para compilar. Puede tener par�metros definidos
	 *            como "?" para rellenarlos despu�s. Por ejemplo,
	 *            "SELECT * FROM books WHERE author = ? LIMIT 30"
	 * 
	 * @return La sentencia SQL compilada.
	 */
	public SQLiteStatement compileStatement(String sql) {
		return mDb.compileStatement(sql);
	}

	public SQLiteStatement getItemInsertStatement() {
		StringBuilder builder = new StringBuilder();
		builder.append("REPLACE INTO ");
		builder.append(Tables.ITEMS);
		builder.append("(");
		builder.append(Columns.ID);
		builder.append(", ");
		builder.append(Columns.Items.STATE);
		builder.append(", ");
		builder.append(Columns.Items.TIME_ADDED);
		builder.append(", ");
		builder.append(Columns.Items.TIME_UPDATED);
		builder.append(", ");
		builder.append(Columns.Items.TITLE);
		builder.append(", ");
		builder.append(Columns.Items.URL);
		builder.append(") VALUES (?, ?, ?, ?, ?, ?);");

		return mDb.compileStatement(builder.toString());
	}

	public SQLiteStatement getTagInsertStatement() {
		StringBuilder builder = new StringBuilder();
		builder.append("INSERT INTO ");
		builder.append(Tables.TAGS);
		builder.append("(");
		builder.append(Columns.Tags.NAME);
		builder.append(") VALUES (?);");

		return mDb.compileStatement(builder.toString());
	}

	public SQLiteStatement getRelItemTagInsertStatement() {
		StringBuilder builder = new StringBuilder();
		builder.append("INSERT INTO ");
		builder.append(Tables.REL_ITEMS_TAGS);
		builder.append("(");
		builder.append(Columns.RelItemsTags.ID_ITEM);
		builder.append(", ");
		builder.append(Columns.RelItemsTags.ID_TAG);
		builder.append(") VALUES (?, ?);");

		return mDb.compileStatement(builder.toString());
	}

	public SQLiteStatement getRelItemTagDeleteStatement() {
		StringBuilder builder = new StringBuilder();
		builder.append("DELETE FROM ");
		builder.append(Tables.REL_ITEMS_TAGS);
		builder.append(" WHERE ");
		builder.append(Columns.RelItemsTags.ID_ITEM);
		builder.append(" = ?");

		return mDb.compileStatement(builder.toString());
	}

	public long getTagIdByName(String name) {
		Cursor cursor = mDb.query(Tables.TAGS, new String[] { Columns.ID },
				Columns.Tags.NAME + " = ?", new String[] { name }, null, null,
				null);
		if (cursor != null && cursor.moveToFirst()) {
			return cursor.getLong(cursor.getColumnIndexOrThrow(Columns.ID));
		}

		return -1;
	}

	// public long insertTag(String name) {
	// ContentValues values = new ContentValues();
	// values.put(Columns.Tags.NAME, name);
	//		
	// // Returns funny stuff
	// return mDb.insertWithOnConflict(Tables.TAGS, null, values,
	// SQLiteDatabase.CONFLICT_IGNORE);
	// }
}
